from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import requests
import time

# 假设已有数据库连接配置
DATABASE_URL = "mysql+pymysql://chishi_erjiakj_c:raBtRNr21rfw2FjB@1.14.97.95/chishi_erjiakj_c"
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()

# 计算时间范围
cutoff_time = datetime.now() - timedelta(hours=1)
              # datetime.now() - timedelta(hours=1)



try:
    # 执行主查询（带 JOIN）
    main_query = text("""
        select id,godate,phone from sys_goods where update_by = 0 and godate < :cutoff_time
    """)

    details = session.execute(main_query, {'cutoff_time': cutoff_time}).fetchall()
    print(details)
    # for det in details:
    #     # 检查用户是否存在（非机器人用户）
    #     user_check = text("""
    #         SELECT id
    #         FROM sys_user
    #         WHERE phone = :phone
    #           AND robot = 1
    #         LIMIT 1
    #     """)
    #     user_exists = session.execute(user_check, {'phone': det.phone}).fetchone()
    #
    #     if not user_exists:
    #         # 发送短信（需实现 send_msg 函数）
    #
    #         data = {'phone': det.phone, 'code': 'date', 'value': det.godate.strftime('%Y-%m-%d'),
    #                 'sms': 'SMS_474240509'}
    #         requests.post('https://chishi.erjiakj.cn/index/daily/alysms', data=data)
    #     # 更新 goods_details
    #     update_details = text("""
    #         UPDATE sys_goods_details
    #         SET update_by = 0,
    #             update_date = :datetime
    #         WHERE id = :details_id
    #     """)
    #     session.execute(update_details, {'datetime':time.strftime("%Y-%m-%d %H:%M:%S"),'details_id': det.details_id})
    #
    #     # 检查空价格并更新 goods
    #     if not det.price:  # 根据实际数据类型可能需要调整判断逻辑
    #         update_goods = text("""
    #             UPDATE sys_goods
    #             SET update_by = 0,
    #                 update_date = :datetime
    #             WHERE id = :goodsid
    #         """)
    #         session.execute(update_goods, {'datetime':time.strftime("%Y-%m-%d %H:%M:%S"),'goodsid': det.goodsid})

    session.commit()
    print('15分钟订单取消成功')
except Exception as e:
    session.rollback()
    print(f"Error occurred: {str(e)}")
    raise
finally:
    session.close()